USE [TimeSheet]
GO
/****** Object:  StoredProcedure [dbo].[AE_SP001_TimeSheet_EmailNotification]    Script Date: 02/11/2015 17:55:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/** Created on : 05/02/2015 
-- Created By : VIVEK
-- Input Fields 
	-- Empty
-- Output Fields
	-- Consultant Id,
	-- Consultant Name,
	-- Consultant Email Id, 
	-- ReportingManager Id, 
	-- ReportingManager Name, 
	-- ReportingManager EmailId,
	-- Lastweek Starting Date,
	-- Lastweek Ending Date,
	-- Formatted Starting Date,
	-- KeyIn Date,
	-- Formatted KeyIn Date,
	-- KeyIn Project Code,
	-- KeyIn Project Name,
	-- KeyIn Project Hours
-- Functions Used
	-- AE_FN001_SplitTheString **/

ALTER PROC [dbo].[AE_SP001_TimeSheet_EmailNotification]
AS
BEGIN
	DECLARE 
	@STARTDATE DATETIME,
	@ENDDATE DATETIME,
	@FORMATTEDSTARTDATE VARCHAR(50)

	--Query to Get the last week start date and end date based on current date

	;WITH parms (CurrentDate) AS (SELECT DATEADD(dd, 0, CURRENT_TIMESTAMP))

	SELECT CurrentDate--,(Select DATEPART(DAY,CurrentDate))
		 , LastWeekMonday   = DATEADD(dd,  0, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
		 , LastWeekSunday   = DATEADD(dd,  6, DATEADD(ww, DATEDIFF(ww, 0, DATEADD(dd, -1, CurrentDate)) - 1, 0))
	INTO #Temp
	FROM parms

	SET @STARTDATE = (SELECT TOP 1 LastWeekMonday FROM #Temp)
	SET @ENDDATE = (SELECT TOP 1 LastWeekSunday FROM #Temp)
	SET @FORMATTEDSTARTDATE = CONVERT(VARCHAR(11),(SELECT TOP 1 LastWeekMonday FROM #Temp))
	
	SELECT T0.UserId AS ConsultantId ,T0.UserName AS ConsultantName,T2.Email AS ConsultantEmailId, T3.ReportToID AS ProjMgrId ,
	T3.ReportToName AS ProjMgrName, T4.Email AS ProjMgrEmailId,@STARTDATE AS LastWeekStartDate ,@ENDDATE AS LastWeekEndDate,
	@FORMATTEDSTARTDATE AS FormattedStartDate,CONVERT(VARCHAR(11),T1.[Date]) AS KeyInDate,T1.PrjCode AS KeyInProjCode, 
	T1.PrjName AS KeyInProjName, IsNull(SUM(T1.Total),0)TotalHours INTO #IMPROPERUSER
	FROM aspnet_Users T0 WITH (NOLOCK)
	LEFT OUTER JOIN(SELECT UserCode , SUM(Hour) AS Total, [Date],PrjCode,PrjName FROM tbl_TimeEntry WITH (NOLOCK) WHERE  [Date] BETWEEN @STARTDATE AND @ENDDATE
	GROUP BY UserCode,[Date],PrjCode,PrjName) T1 ON T1.UserCode = T0.UserName 
	LEFT JOIN TimeSheet.dbo.aspnet_Membership T2 WITH (NOLOCK) ON T2.UserId = T0.UserId
	LEFT JOIN TimeSheet.dbo.tbl_ReportTo T3 WITH (NOLOCK) ON T3.UserId = T2.UserId
	LEFT JOIN TimeSheet.dbo.aspnet_Membership T4 WITH (NOLOCK) ON T4.UserId = T3.ReportToID
	--LEFT JOIN TimeSheet.dbo.tbl_UsersAdd T5 WITH (NOLOCK) ON T5.UserId = T0.UserId
	--WHERE T5.DateResigned > @ENDDATE
	GROUP BY T0.UserId ,T0.UserName,T2.Email,T3.ReportToID,T3.ReportToName,T4.Email,T1.[Date],T1.PrjCode,T1.PrjName 
	
	DELETE FROM #IMPROPERUSER WHERE ConsultantId IN (SELECT UserId FROM TimeSheet.dbo.tbl_UsersAdd where DateResigned > @ENDDATE)
	
	SELECT ConsultantId,SUM(ISNULL(TotalHours,0)) as TotalHours INTO #TempMain
	FROM #IMPROPERUSER GROUP BY ConsultantId
	Having SUM(ISNULL(TotalHours,0))<40
	
	SELECT * FROM #IMPROPERUSER WHERE ConsultantId in (select ConsultantId from #TempMain)
	
	--AND ConsultantId IN ('B9519E02-233E-4E2A-9749-65E8192A8CA1')
	
	--AND ConsultantId IN ('7A11E25B-7CFD-4462-8540-988A63F21C52','9D102FF4-6A2E-4188-B808-9271FC9798D2',
	--					 								'E9552D41-C7C6-4254-8BD6-027A21DA5866','9985B0D1-BA56-46A7-BE4D-B838510FFD7B')
	--
		
	ORDER BY  ConsultantName,KeyInDate,KeyInProjName,TotalHours ASC
	
	DROP TABLE #IMPROPERUSER
	DROP TABLE #Temp
	drop table #TempMain
	
END	

	